﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Simple.Services.System.QHSEViolationCategory.Models;

public class ViolationObserveAnalysisService
{
    private readonly SimpleDbContext _context;
    private readonly QHSEViolationObserverService _observerService;



    public ViolationObserveAnalysisService(SimpleDbContext context, QHSEViolationObserverService observerService)
    {
        _context = context;
        _observerService = observerService;

    }
    private static string EscapeLikePattern(string pattern)
    {
        return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
    }
    public async Task<List<ViolationAnalysisResult>> ViolationObserveAnalysisAsync(ViolationAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();
        if (!string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysUser>(), // Join with User table
                                violation => violation.UserId,
                                user => user.UserId,
                                (violation, user) => new { Violation = violation, User = user })
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.User.OrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult.Violation, User = joinResult.User, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            List<QHSEViolationObserverModel> observerModels =await _observerService.GetObserverAsync();
            if(observerModels.Count > 0)
            {
                foreach( var observerModel in observerModels)
                {
                    var value = await query.Where(u => u.ObserveComm.Contains(observerModel.Item1)).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = observerModel.Item1;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<List<ViolationAnalysisResult>> ViolationObserve2AnalysisAsync(ViolationAnalysisInput input,string name)
    {
        List<ViolationAnalysisResult> result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();
        if (!string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysUser>(), // Join with User table
                                violation => violation.UserId,
                                user => user.UserId,
                                (violation, user) => new { Violation = violation, User = user })
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.User.OrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult.Violation, User = joinResult.User, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            List<QHSEViolationObserverModel> observerModels = await _observerService.GetObserver2Async(name);
            if (observerModels.Count > 0)
            {
                foreach (var observerModel in observerModels)
                {
                    var value = await query.Where(u => u.ObserveComm.Contains(observerModel.Item1+";"+observerModel.Item2) || u.ObserveComm.Contains(observerModel.Item1 + observerModel.Item2)).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = observerModel.Item2;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        return result.OrderByDescending(result => result.Value).ToList();
    }

}
